package com.woniu.microchargeserver.common.mapper;

import com.woniu.microchargeserver.common.bean.dtobean.CustomerChargeTotalDto;
import com.woniu.microchargeserver.common.bean.dtobean.RoomChargeDto;
import com.woniu.microchargeserver.common.bean.dtobean.RoomChargeProDto;
import com.woniu.microcommonsserver.entity.ChargedItemsInfoEntity;
import com.woniu.microcommonsserver.entity.RoomChargedItemsInfoEntity;
import org.apache.ibatis.annotations.*;

import java.util.HashMap;
import java.util.List;

/**房间、收费中间表的mapper
 * @author: hejie
 * @date: 2021/1/25 10:26
 * @description:
 */
@Mapper
public interface RoomChargeMapper {

    /**
     * 分页查询数据:通过房间id查询 该房间的收费项目
     * @author hejie
     * @param column 排序的字段
     * @param order 升序还是降序
     * @param roomId 房间的id
     * @return
     */
    List<RoomChargeDto> findRoomChargeItemByRoomId(@Param("roomId") Long roomId,
                                                   @Param("column")String column,
                                                   @Param("order")String order);

    /**
     * 修改：房间、项目中间表
     * @param roomChargedItemsInfoEntity
     */
    int update(@Param("a") RoomChargedItemsInfoEntity roomChargedItemsInfoEntity);

    /**
     *  每天定时任务1 sql1：结束时间到了，设置“收费项目中间表”  amount_receivable 和 total_payable 设置为项目的价格：收费表、中间表 联查
     *      条件：1.计费结束时间到了，中间表amount_receivable、total_payable其中一个为0或null
     *        2.没有支付 （即r.complete_payment_status=2）
     */
    @Update("update room_charged_items_info r,charged_items_info c  set r.amount_receivable=c.billi_unit_price * r.num *r.magnification , " +
            "r.total_payable=c.billi_unit_price * r.num * r.magnification " +
            " where r.fk_charged_items_id= c.id and r.complete_payment_status=2 " +
            "and r.bill_end_date < now() " +
            "")
//            "and ( (r.amount_receivable IS NULL or r.amount_receivable=0) or (r.total_payable IS NULL or r.total_payable=0) )")
    int updateReceivablePayable();

    /**
     *  每天定时任务2 sql2：
     *    1.结束时间 + 违约天数 >now()：计算违约金： 违约金率*(now-违约天数-结束时间) ：收费表、中间表 联查
     *    2.没有支付（即r.complete_payment_status=2）
     *    DATEDIFF(date1,date2)    两个日期相减函数，返回date1-date2相差的天数
     */
    @Update("update room_charged_items_info r,charged_items_info c " +
            "set r.liquidated_damages = c.late_fee_rate * ( DATEDIFF(now(),r.bill_end_date) - c.overdue_pay_dayst )  " +
            " where (  r.fk_charged_items_id= c.id and " +
            "( DATEDIFF(now(),r.bill_end_date) - c.overdue_pay_dayst ) >0 " +
            " and r.complete_payment_status=2 " +
            ")")
    int updateLiquidatedDamages();

    /**
     * 新增 一个收费项目、房间的中间表
     * @author hejie
     *     注意：impl中根据“收费项目”中的“周期单位”自己设置“开始计费时间和结束计费时间”
     *     complete_payment_status:1-完成支付，2-未支付'
     *
     *     新增收费：  AmountReceivable
     *   amount_receivable 和 total_payable 设置为项目的价格，magnification=1，liquidated_damages=0，ladder_mode=1，input_time=now
     *   approval_status=2，complete_payment_status=2，exemption_registration_status=2,num=1
     * @return
     */
    @Options(
            useGeneratedKeys = true,
            keyProperty = "a.id"
    )
    @Insert("insert into room_charged_items_info " +
            "(fk_charged_items_id,fk_room_id, bill_start_date, bill_end_date, complete_payment_status,amount_receivable," +
            " total_payable,magnification,liquidated_damages,ladder_mode,input_time,approval_status,exemption_registration_status,num)" +
            " values(#{a.chargedItemsInfoEntity.id},#{a.spaceInfoEntity.id},#{a.billStartDate},#{a.billEndDate},2,#{a.amountReceivable}," +
            " #{a.totalPayable},1,0,1,now(),2,2,1)" )
    void addRoomCharge(@Param("a") RoomChargedItemsInfoEntity roomCharge);

    /**
     * 新增 一个收费项目、房间的中间表
     * @author hejie
     * @param id:根据 房间、收费项目中间表的id：删除一个中间表的记录
     * @return
     */
    @Delete("delete from room_charged_items_info where id=#{id}")
    int delRoomCharge(Long id);

    /**
     * 根据房间id，查询该房间的所有 “收费项目
     * @param sid 房间id
     *            complete_payment_status:1-完成支付，2-未支付'
     * @return
     */
    @Select("select c.charge_name as chargeName,r.amount_receivable as amountReceivable " +
            "from charged_items_info as c,room_charged_items_info as r " +
            "where r.fk_charged_items_id=c.id and r.fk_room_id=#{sid} and r.complete_payment_status=2")
    List<HashMap<String,Double>> findBySId(Long sid);

    /**
     * 根据房间id 查询3个合计：应收合计，优惠金额总计，减免金额总计
     * @param roomId 房间id
     *               complete_payment_status:1-完成支付，2-未支付'
     * @return
     */
    @ResultType(CustomerChargeTotalDto.class)
    @Select("select sum(r.amount_receivable) as amountReceivableTotal,sum(r.preferential_amount) as preferentialAmountTotal," +
            " sum(r.deduction_amount) as deductionAmountTotal" +
            " from charged_items_info as c,room_charged_items_info as r" +
            " where r.fk_charged_items_id=c.id and r.fk_room_id=#{roomId} and r.complete_payment_status=2")
    CustomerChargeTotalDto findTotalByrid(Long roomId);

    /**charge_type
     *根据 房间id 查询该房间的 预收金额之和、押金金额之和（要根据传入的charge_type决定查询 预收金还是押金）
     * @param roomId 房间id
     * @param chargeType：收费类型是 预收金还是押金：1-押金，2-预收金
     * @return
     */
    @ResultType(CustomerChargeTotalDto.class)
    @Select("select sum(a.remain_money) " +
            "from advance_deposit_info a,room_advance_deposit_info r_a " +
            "where a.id = r_a.fk_advance_and_deposit_id and r_a.fk_room_id = #{roomId} and charge_type=#{chargeType}")
    Double findaDvanceDepositTotalByrid(@Param("roomId") Long roomId,@Param("chargeType") Byte chargeType);


}
